﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;
using System.Data;
using System.Configuration;

namespace AccessDAL {
    /// <summary>
    /// Access为文件数据库，不存在存储过程，所以没有CommandType参数
    /// </summary>
    class OleDbHelper {
        private static string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ConfigurationManager.AppSettings["DbPath"].ToString();
        /// <summary>
        /// 获取、设置数据库连接字符串
        /// </summary>
        public static string ConnectionString {
            set { connStr = value; }
            get { return connStr; }
        }
        /// <summary>
        /// 获取表结果
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string commandText, OleDbParameter[] parameters) {
            DataTable dt = new DataTable();
            OleDbConnection conn = new OleDbConnection(ConnectionString);
            OleDbCommand cmd = new OleDbCommand();
            if (parameters != null) {
                foreach (OleDbParameter parameter in parameters) {
                    cmd.Parameters.Add(parameter);
                }
            }
            cmd.Connection = conn;
            cmd.CommandText = commandText;
            conn.Open();
            OleDbDataAdapter oda = new OleDbDataAdapter(cmd);
            oda.Fill(dt);
            conn.Close();
            return dt;
        }
        public static DataTable ExecuteDataTable(string commandText) {
            return ExecuteDataTable(commandText, null);
        }
        /// <summary>
        /// 获取DataReader结果
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static OleDbDataReader ExecuteReader(string commandText, OleDbParameter[] parameters) {
            OleDbConnection conn = new OleDbConnection(ConnectionString);
            OleDbCommand cmd = new OleDbCommand();
            if (parameters != null) {
                foreach (OleDbParameter parameter in parameters) {
                    cmd.Parameters.Add(parameter);
                }
            }
            cmd.Connection = conn;
            cmd.CommandText = commandText;
            conn.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        public static OleDbDataReader ExecuteReader(string commandText) {
            return ExecuteReader(commandText, null);
        }
        /// <summary>
        /// 返回查询结果中第 1 行第 1 列的值
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string commandText, OleDbParameter[] parameters) {
            OleDbConnection conn = new OleDbConnection(ConnectionString);
            OleDbCommand cmd = new OleDbCommand();
            if (parameters != null) {
                foreach (OleDbParameter parameter in parameters) {
                    cmd.Parameters.Add(parameter);
                }
            }
            cmd.Connection = conn;
            cmd.CommandText = commandText;
            conn.Open();
            object w = cmd.ExecuteScalar();
            conn.Close();
            return w;
        }
        public static object ExecuteScalar(string commandText) {
            return ExecuteScalar(commandText, null);
        }
        /// <summary>
        /// 执行增/删/改/后，返回受影响的行数
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string commandText,OleDbParameter[] parameters) {
            OleDbConnection conn = new OleDbConnection(ConnectionString);
            OleDbCommand cmd = new OleDbCommand(commandText,conn);
            if (parameters != null) {
                foreach(OleDbParameter parameter in parameters) {
                    cmd.Parameters.Add(parameter);
                }
            }
            conn.Open();
            int n = cmd.ExecuteNonQuery();
            conn.Close();
            return n;
        }
        public static int ExecuteNonQuery(string commandText) {
            return ExecuteNonQuery(commandText, null);
        }
    }
}
